package doInterce.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import doInterce.DoInterface;
import utils.*;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @Description: 批量执行茂名脚本
 * @Author: syh
 * @Date: 2024/01/19/15:48
 */
public class DoBatSql implements DoInterface {
    @Override
    public void doRun(Connection connection,String customParam) throws Exception {
        String dirPath = PathUtil.getOutFilePathByFileName("sql");
        String logPath = PathUtil.getOutFilePathByFileName("log.txt");
        String excelDir = PathUtil.getOutFilePathByFileName("excelDir");
        String sqlLogPath = PathUtil.getOutFilePathByFileName("sqlLog.txt");

        /**
         * 删除excel所在目录
         */
        File excelDirFile = new File(excelDir);
        if(excelDirFile.exists()){
            FileUtil.del(excelDir);
        }
        excelDirFile.mkdirs();


        File logFile = new File(logPath);
        FileUtil.del(logFile);
        logFile.createNewFile();

        File sqlLogFile = new File(sqlLogPath);
        FileUtil.del(sqlLogFile);
        sqlLogFile.createNewFile();

        Statement statement = connection.createStatement();
        /**
         * 挨个遍历脚本文件，并执行
         */
        File[] listFiles = new File(dirPath).listFiles();
        if(listFiles == null){
            System.out.println("没有找到文件，停止运行");
            return;
        }

        System.out.println("查找到"+listFiles.length+"个文件");
        int fileIndex = 1;
        for(File sqlFile : listFiles) {

            FileUtil.appendString("开始执行"+sqlFile.getName()+"，进度 "+fileIndex+"/"+listFiles.length+" \n",logFile,"UTF-8");
            System.out.println("开始执行"+sqlFile.getName()+"，进度 "+fileIndex+"/"+listFiles.length);

            long bigStart = System.currentTimeMillis();
            List<String> sqlScriptList = getSqlScript(logFile, sqlFile,customParam);

            if(customParam.contains(ConstantUtil.SQL_BATCH_EXECUTE)){
                connection.setAutoCommit(false);
                BatchSqlUtil.batchDo(connection,sqlScriptList,10000,sqlLogFile);
            }else {
                for (int i = 0; i < sqlScriptList.size(); i++) {
                    String sql = sqlScriptList.get(i);
                    if(sql.endsWith(";")){
                        sql = sql.substring(0, sql.length() - 1);
                    }
                    FileUtil.appendString(sql + "\n", sqlLogFile, "UTF-8");
                    long start = System.currentTimeMillis();

                    if (sql.toUpperCase().trim().startsWith("SELECT")) {
                        ResultSet resultSet = statement.executeQuery(sql);
                        boolean isSuccess = writeExcel(excelDir, sqlFile, resultSet);
                        if (!isSuccess) {
                            FileUtil.appendString("第" + i + "条sql执行失败\n", logFile, "UTF-8");
                            throw new Exception("第" + i + "条sql执行失败");
                        }
                        resultSet.close();
                    } else {
                        statement.executeUpdate(sql);
                    }
                    FileUtil.appendString("------------------------------\n", sqlLogFile, "UTF-8");
//                System.out.println("------------------------------");
                    long end = System.currentTimeMillis();
                    FileUtil.appendString("第" + i + "条sql执行总共耗时" + (end - start) / 1000 + "秒\n", logFile, "UTF-8");
                }
            }
            long bigEnd = System.currentTimeMillis();


            FileUtil.appendString(sqlFile.getName()+"执行总共耗时"+(bigEnd-bigStart)/1000+"秒"+"，进度 "+fileIndex+"/"+listFiles.length+"\n",logFile,"UTF-8");
            System.out.println(sqlFile.getName()+"执行总共耗时"+(bigEnd-bigStart)/1000+"秒"+"，进度 "+fileIndex+"/"+listFiles.length);
            fileIndex++;
            System.out.println("----------------------------------------------------------------");
            FileUtil.appendString("----------------------------------------------------------------\n",logFile,"UTF-8");
        }

        statement.close();
        FileUtil.appendString("执行完成\n",logFile,"UTF-8");
    }

    /**
     * 删除注释
     * @param file
     * @return
     */
    public static String clearRubbish(File file) {
        List<String> contentList = FileUtil.readUtf8Lines(file);

        boolean isStart = false;
        Iterator<String> iterator = contentList.iterator();
        while (iterator.hasNext()){
            String content = iterator.next();
            content = content.toUpperCase().trim();
            if(StrUtil.isBlank(content.trim())){
                iterator.remove();
                continue;
            }

            /**
             * 删除单行注释
             */
            if(content.toUpperCase().startsWith("-- ")){
                iterator.remove();
                continue;
            }

            /**
             * 删除多行注解
             */
            if(content.toUpperCase().startsWith("/*")){
                iterator.remove();
                isStart = true;
                continue;
            }else if(content.toUpperCase().startsWith("*/")){
                iterator.remove();
                isStart = false;
            }

            if(isStart){
                iterator.remove();
            }

        }

        return CollectionUtil.join(contentList,"\n");
    }


    /**
     * 结果写入excel
     * @param excelDir
     * @param sqlFile
     * @param resultSet
     * @return
     */
    public boolean writeExcel(String excelDir,File sqlFile,ResultSet resultSet) {
        try {
            //表头
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> headList = new ArrayList<>();
            for(int i = 1; i <= columnCount; i++){
                headList.add(metaData.getColumnLabel(i));
            }

            List<List<String>> dataList = DBUtil.getList(resultSet, columnCount);

            ExportExcelUtilsWithEasy.doExport(excelDir+"/"+sqlFile.getName().replace(".sql",".xlsx"), dataList, headList);

        }catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    /**
     * 解析sql文件的sql
     * @param logFile
     * @param sqlFile
     * @return
     */
    public static List<String> getSqlScript(File logFile,File sqlFile,String customParam){
        List<String> sqlList = new ArrayList<>();

        if(sqlFile.getName().contains("函数集")){
            sqlList.addAll(getFunctionSql(sqlFile));
            return sqlList;
        }

        //删除注释
        String context = clearRubbish(sqlFile);

        String[] sqlArr = null;
        if(customParam.contains(ConstantUtil.SEPARATOR_LINE)){
            sqlArr = context.split("\n");
        }else{
            sqlArr = context.split(";");
        }
        int index = 0;
        for (String sql : sqlArr) {
            if (sql == null || sql.trim().isEmpty()) {
                continue;
            }
            sqlList.add(sql);
            index++;
        }
        FileUtil.appendString(sqlFile.getName()+"找到"+index+"条sql\n",logFile,"UTF-8");

        return sqlList;
    }

    public static List<String>  getFunctionSql(File file){
        List<String> contentList = FileUtil.readUtf8Lines(file);

        List<String> sqlList = new ArrayList<>();
        StringBuffer sqlBuf = new StringBuffer();
        boolean isStart = false;
        for(String content : contentList){
            content = content.trim();
            if(StrUtil.isBlank(content.trim())){
                continue;
            }

            if(content.toUpperCase().startsWith("DROP")){
                sqlList.add(content);
                continue;
            }else if(content.toUpperCase().startsWith("CREATE")){
                sqlBuf.append(content+"\n");
                isStart = true;
                continue;
            }else if(content.toUpperCase().startsWith("END")){
                sqlBuf.append(content+"\n");
                sqlList.add(sqlBuf.toString());
                isStart = false;
                sqlBuf.setLength(0);
            }

            if(isStart){
                sqlBuf.append(content+"\n");
            }

        }

        return sqlList;
    }
}
